'use strict';

const Service = require('egg').Service;

class UserService extends Service {
    //yes
    //插入用户数据
    async register(users) {
        try {
            let result = await this.app.mysql.insert('users', {
                username: users.username,
                password: users.password,
                email: users.email,
                userAvatar: users.avatar,
                userCreateTime: users.createTime
            });
            return result;
        } catch (e) {
            return false;
        }
    };
    //yes
    //根据username查询用户
    async getUserByUsername(username) {
        try {
            let result = await this.app.mysql.get('users', { username: username });
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };
    //yes
    //根据用户id查询用户 用于判断用户是否存在
    async getUserById(uid) {
        try {
            let result = await this.app.mysql.get('users', { uid: uid });
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };
    // yes
    //根据用户id查询用户  用户主页使用
    async getUserHomeById(uid, peopleid) {
        try {
            let result = await this.app.mysql.query(
                `select uid,username,
                 email,userCreateTime as createDate,
                 userIntroduction as introduction,
                 userAvatar as avatar, 
                 if(isnull(f.followerId),0,1) as isfollow
                 from users 
                 left join follows as f
                 on f.followerId = '${uid}'
                 and f.followeeId = ${peopleid}
                 where uid = ${peopleid}
                 limit 1`
            )
            if (result) {
                return result;
            } else {
                return false
            }
        } catch (e) {
            return false
        }
    };
    // yes
    //获取所有分类信息
    async getCategoryList() {
        try {
            let result = await this.app.mysql.select('categorys')
            if (result) {
                let temp = result.map(item => {
                    let { categoryId, ...others } = item;
                    return {
                        ...others,
                        categoryId: parseInt(categoryId)
                    }
                })

                return temp;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    }

    // yes
    //用户发布内容
    async write(article) {
        try {
            let result = await this.app.mysql.insert('articles', {
                screenShot: article.screenshot,
                title: article.title,
                articleSummaryInfo: article.summaryInfo,
                articleContent: article.content,
                categoryId: article.categoryId,
                articlePostTime: article.postTime,
                uid: article.userid
            });
            // 判断插入成功
            let insertSuccess = result.affectedRows === 1;
            if (insertSuccess) {
                return true
            } else {
                return false
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //分页获取用户发布的文章
    async getArticlesByPage(data) {
        try {
            let result = await this.app.mysql.query(
                `select a.articleId, a.screenShot,a.title,a.articleSummaryInfo,a.articleViews,a.articlePostTime,a.articleLikes,a.articleComments,
                c.categoryId,c.categoryName,c.categoryTitle,
                u.uid,username,u.userIntroduction,u.userAvatar,
                if(isnull(l.uId),0,1) as islike
                from (articles as a,users as u,categorys as c)
                left join likes as l
                on l.uId = '${data.myid}'
                and l.articleId = a.articleId
                where a.uid = ${data.peopleid} and a.uid = u.uid  
                and a.categoryId = c.categoryId   
                order by a.articleId desc 
                limit ${data.currentsize},${data.size} `
            )
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return e;
        }
    };
    // yes
    //根据文章id获取文章内容
    async getArticlesById(data) {
        // return data;
        try {
            let result = await this.app.mysql.query(
                `select a.articleId as articleid,
                a.screenShot as screenshot,
                a.title,a.articleContent as content,
                a.articleViews as viewcount,
                a.articleLikes as likes,
                a.articleComments as comments,
                a.articlePostTime as posttime,
                c.categoryId as categoryid,
                c.categoryTitle as categorytitle,
                c.categoryName as categoryname,
                u.uid as userid, 
                u.username as username,
                u.email as email,
                u.userAvatar as avatar,
                if(isnull(l.uId),0,1) as islike,
                if(isnull(f.followerId),0,1) as isfollow,
                if(
                (select if(count(*)>0,0,1) as isIn 
                from favorites f
                left join favoriteitems as fi
                on fi.favoriteId = f.favoriteId
                and fi.articleId = ${data.articleid}
                where f.uid = '${data.uid}'
                and articleId = ${data.articleid}
                limit 1)>0,0,1) as isfavorite
                from (articles as a, categorys as c ,users as u)
                left join likes as l
                on l.uId = '${data.uid}'
                and l.articleId = a.articleId
                left join follows as f
        		on f.followerId = '${data.uid}'
        		and f.followeeId = a.uid
                where a.articleId = ${data.articleid}
                and a.categoryId = c.categoryId 
                and a.uid = u.uid`
            );
            if (result) {
                return result
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };




    // yes
    //根据用户id 关注用户
    async followUser(followUser) {
        try {
            //关注前先判断用户是否已经关注了目标，若已关注，则return ,否者继续插入
            let checkFollow = await this.app.mysql.get('follows', {
                followerId: followUser.followerid,
                followeeId: followUser.followeeid,
            });
            if (!checkFollow) {
                //插入数据
                let follow = await this.app.mysql.insert('follows', {
                    followerId: followUser.followerid,
                    followeeId: followUser.followeeid,
                    createAt: followUser.createat
                });
                let insertSuccess = follow.affectedRows === 1;
                if (insertSuccess) {
                    return true
                } else {
                    return false
                }
            } else {
                return true;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //根据用户id 取消关注用户
    async unfollowUser(unfollowUser) {
        try {
            let result = await this.app.mysql.query(`delete from follows where followerId = ${unfollowUser.followerid} and followeeId = ${unfollowUser.followeeid}`);
            let deleteSuccess = result.affectedRows === 1
            if (deleteSuccess) {
                return true
            } else {
                return false
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //获取用户关注数
    async getFollowerCount(uid) {
        try {
            let followerCount = this.app.mysql.query(
                `select * from follows where followerId = ${uid}`
            );
            if (followerCount) {
                return followerCount;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //获取用户粉丝数
    async getFollowingCount(uid) {
        try {
            let followerCount = this.app.mysql.query(
                `select * from follows where followeeId = ${uid}`
            );
            if (followerCount) {
                return followerCount;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };

    //分页获取用户关注的人员
    async getFollowings(myid, peopleid, size, currentsize) {
        try {
            let result = await this.app.mysql.query(
                `select
                u.uid,
                u.username,
                u.email,
                u.userAvatar,
                if(isnull(fo.followerId),0,1) as isfollow
                from 
                (users as u
                ,follows as f)
                left join follows as fo
                on fo.followerId = '${myid}'
                and fo.followeeId = u.uid
                where f.followerId = '${peopleid}'
                and f.followeeId = u.uid
                limit ${currentsize},${size}`
            )
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };

    //分页获取用户粉丝
    async getFollowers(myid, peopleid, size, currentsize) {
        try {
            let result = await this.app.mysql.query(
                `select u.uid,
                 u.username,
                 u.email,
                 u.userAvatar,
                 if(isnull(fo.followerId),0,1) as isfollow 
                 from (users as u,follows as f) 
                 left join follows as fo
                 on fo.followerId = '${myid}'
                 and fo.followeeId = u.uid
                 where f.followeeId = '${peopleid}'
                 and f.followerId = u.uid
                 limit ${currentsize},${size}`
            )
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //点赞文章
    async likeArticle(uid, articleid, date) {
        try {
            //点赞前先判断是否已点赞该文章，若已关注，则return,否者继续插入
            let checkLike = await this.app.mysql.get('likes', {
                uId: uid,
                articleId: articleid
            });
            if (!checkLike) {
                //利用mysql事务 当用户点赞成功后该篇文章点赞数改变更新
                let transaction = await this.app.mysql.beginTransaction();
                try {
                    await transaction.insert('likes', {
                        uId: uid,
                        articleId: articleid,
                        createAt: date
                    });
                    await transaction.query(
                        `update articles set articleLikes = (select count(*) from likes where articleId = ${articleid}) where articleId = ${articleid}`
                    )
                    await transaction.commit(); //提交事务
                    return true;
                } catch (e) {
                    await transaction.rollback(); //回滚事务
                    return false;
                }
            } else {
                return true;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //取消点赞文章
    async unlikeArticle(uid, articleid) {
        try {
            //取消点赞前先判断用户是否未点赞，若未点赞，则return，否则继续删除
            let checkUnlike = await this.app.mysql.get('likes', {
                uId: uid,
                articleId: articleid
            });
            if (checkUnlike) {
                //利用mysql事务 当用户取消点赞后该篇文章点赞数改变更新
                let transaction = await this.app.mysql.beginTransaction();
                try {
                    await transaction.delete('likes', {
                        uId: uid,
                        articleId: articleid,
                    });
                    await transaction.query(
                        `update articles set articleLikes = (select count(*) from likes where articleId = ${articleid}) where articleId = ${articleid}`
                    );
                    await transaction.commit(); //提交事务
                    return true;
                } catch (e) {
                    await transaction.rollback(); //回滚事务
                    return false;
                }

            } else {
                return true;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //根据文章分类来分页获取文章数据
    async getListByCategory(categoryid, currentsize, uid, size) {
        try {
            let getList = await this.app.mysql.query(
                `select 
                a.articleId, a.screenShot, a.title, a.articleSummaryInfo, a.articlePostTime, a.articleLikes, a.articleComments,
                c.categoryId,c.categoryName,c.categoryTitle,
                u.uid,u.username,u.userAvatar,
                if(isnull(l.uId),0,1) as islike
                from (articles as a,users as u,categorys as c)
                LEFT JOIN likes as l
                ON l.articleId = a.articleId
                and l.uId = '${uid}'
                where a.uid =  u.uid  
                and a.categoryId = c.categoryId
                and a.categoryId = ${categoryid}
                order by a.articlePostTime desc
                limit ${currentsize}, ${size}`
            );
            if (getList) {

                return getList;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    };

    // yes
    //用户获取文章点赞总数 && 文章被阅读总数
    async getLikedAndViewdCount(uid) {
        try {
            let result = await this.app.mysql.query(
                `select ifnull(sum(articleLikes),0) as likeCount,ifnull(sum(articleViews),0) as viewCount from articles where uid = '${uid}'`
            );
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    }


    // yes
    //用户获取收藏夹列表，并判断该文章是否被已被该收藏夹收藏
    async getFavoriteList(uid, articleid) {
        try {
            //获取收藏夹列表
            let result = await this.app.mysql.query(
                `select 
                f.favoriteId,
                f.favoriteName,
                if(isnull(fi.articleId),0,1) as isIn
                from favorites f
                left join favoriteitems as fi
                on fi.favoriteId = f.favoriteId
                and fi.articleId = ${articleid}
                where f.uid = ${uid}`
            );
            if (result) {
                return result;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    }


    //删除收藏夹
    async deleteFavorites(uid, favoriteid) {
        try {
            //联表设计，只删除主表。
            let result = await this.app.mysql.delete('favorites', {
                favoriteId: favoriteid,
                uid: uid
            })
            let deleteSuccess = result.affectedRows >= 0;
            if (deleteSuccess) {
                return true;
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    }

    // yes
    //收藏文章
    async collection(uid, articleid, favoriteid) {
        try {
            //先判断此用户是否存在该收藏夹
            let checkFavorites = await this.app.mysql.get('favorites', { favoriteId: favoriteid, uid: uid });
            if (checkFavorites) {
                //判断此收藏夹是否已收藏该文章
                let checkIsIn = await this.app.mysql.get('favoriteItems', { favoriteId: favoriteid, articleId: articleid });
                if (!checkIsIn) {
                    // //插入数据
                    let transaction = await this.app.mysql.beginTransaction();

                    try {
                        transaction.insert('favoriteItems', {
                            favoriteId: favoriteid,
                            articleId: articleid
                        });
                        let res = transaction.query(
                            `select
                            if(count(*)=0,0,1) as isIn
                            from favorites f
                            left join favoriteitems as fi
                            on fi.favoriteId = f.favoriteId
                            and fi.articleId = ${articleid}
                            where f.uid = ${uid}
                            and articleId =  ${articleid}
                            limit 1`
                        );
                        await transaction.commit();
                        return res
                    } catch (e) {
                        await transaction.rollback();
                        return false;
                    }

                } else {
                    return true;
                }
            } else {
                return false;
            }
        } catch (e) {
            return false;
        }
    }

    // yes
    //取消收藏文章
    async cancelCollection(uid, articleid, favoriteid) {
        let transaction = await this.app.mysql.beginTransaction();
        try {
            await transaction.query(
                `delete from favoriteItems where articleId = ${articleid} and favoriteId = ${favoriteid} and favoriteId in (select favoriteId from favorites where uid=${uid})`
            )

            let res = await transaction.query(
                `select
                if(count(*)=0,0,1) as isIn
                from favorites f
                left join favoriteitems as fi
                on fi.favoriteId = f.favoriteId
                and fi.articleId = ${articleid}
                where f.uid = ${uid}
                and articleId =  ${articleid}
                limit 1`
            );
            await transaction.commit();
            return res

        } catch (e) {
            await transaction.rollback();
            return false;
        }
    }



    // yes
    //用户新建收藏夹,并收藏文章
    async addFolder(uid, articleid, foldername, favoritesImage) {
        let transaction = await this.app.mysql.beginTransaction();
        try {
            let res = await transaction.query(
                `insert into favorites(uid,favoriteName,favoriteImage) values(${uid},'${foldername}','${favoritesImage}') `
            )
            await transaction.query(
                `insert into favoriteItems(favoriteId,articleId) values(${res.insertId},${articleid})`
            )
            await transaction.commit();
            return true;
        } catch (e) {
            await transaction.rollback();
            return false;
        }
    };
    // yes
    async getFavoriteListInfo(uid) {
        try {
            let res = this.app.mysql.query(
                `select 
                f.favoriteId, 
                f.favoriteName,
                f.favoriteImage,
                ifnull(fi.total,0) as count
                from favorites f 
                left join
                (select favoriteId, count(favoriteId)  as total from favoriteItems group by favoriteId) fi
                on f.favoriteId = fi.favoriteId 
                where f.uid = '${uid}'`
            )
            if (res) {
                return res
            } else {
                return false
            }
        } catch (e) {
            return false
        }
    };
    // yes
    async checkFavoriteId(favoriteid) {
        try {
            let res = this.app.mysql.get('favorites', { favoriteId: favoriteid })
            if (res) {
                return res;
            } else {
                return false;
            }
        } catch (e) {
            return false
        }
    };
    // yes
    async getArticlesByFavoriteId(favoriteid, myid) {
        try {
            let res = this.app.mysql.query(
                `select 
                 a.articleId, 
                 a.screenShot,
                 a.title,
                 a.articleSummaryInfo, 
                 a.articleViews,
                 a.articlePostTime,
                 a.articleLikes,
                 a.articleComments,
                 a.categoryId,
                 u.uid as userid, 
                 u.username as username,
                 u.email as email,
                 u.userAvatar as avatar,
                 if(isnull(l.uId),0,1) as islike
                 from 
                 (articles as a,
                 favoriteItems as fi,
                 users as u)
                 LEFT JOIN likes as l
                 ON l.articleId = a.articleId
                 and l.uId = '${myid}'
                 where a.articleId = fi.articleId
                 and fi.favoriteId = ${favoriteid}
                 and a.uid = u.uid`
            );
            if (res) {
                return res;
            } else {
                return false;
            }
        } catch (e) {
            return false
        }
    };

    //更新用户头像
    async updateAvatar(avatar, uid) {
        try {
            const res = await this.app.mysql.query(
                `update  users 
                 set userAvatar = '${avatar}' 
                 where uid = '${uid}'`
            )
            if (res) {
                return true
            } else {
                return false
            }
        } catch (e) {
            return false
        }
    };
    //更新用户信息
    async updateUserInfo(uid, username, email, introduction) {
        try {
            const res = await this.app.mysql.query(
                `update  users 
                 set
                 username = '${username}',
                 email='${email}',
                 userIntroduction='${introduction}'
                 where uid = '${uid}'`
            )
            if (res) {
                return true
            } else {
                return false
            }
        } catch (e) {
            return false
        }
    };

    //获取所有评论信息
    async getAllComments(articleid) {
        let transaction = await this.app.mysql.beginTransaction();
        try {
            const res = await transaction.query(
                `select
                a.articleComments,
                cmt.comments_id,
                cmt.comments_content,
                cmt.comments_date,
                cmt.user_id,
                u.username,
                u.email,
                u.userAvatar,
                if(isnull(rps.reply_id),0,1) as hasreply,
                rps.comments_id as reply_comments_id,
                rps.reply_id,
                rps.from_user_id,
                users1.username as from_user_name,
                users1.userAvatar as from_user_avatar,
                users2.uid as to_user_id,
                users2.username as to_user_name,
                rps.reply_content,
                rps.reply_date
                from 
                (comments as cmt,
                users as u)
                left join replys as rps
                on rps.comments_id = cmt.comments_id
                left join users as users1
                on users1.uid = rps.from_user_id
                left join users as users2
                on users2.uid = rps.to_user_id
                left join articles as a
                on a.articleId = '${articleid}'
                where article_id = '${articleid}'
                and u.uid = cmt.user_id
                order by comments_date desc`
            )
            await transaction.commit();
            return res;
        } catch (e) {
            await transaction.rollback();
            return false;
        }
    };

    // 添加回复
    async sendReply(comments_id, from_user_id, to_user_id, reply_content, date) {
            let transaction = await this.app.mysql.beginTransaction();
            try {
                const res = await transaction.insert('replys', {
                    comments_id: comments_id,
                    from_user_id: from_user_id,
                    to_user_id: to_user_id,
                    reply_content: reply_content,
                    reply_date: date
                });
                const update = await transaction.query(
                    `
                update articles set articleComments = 
                (select 
                    sum(
                        (select count(*) from comments where article_id = (select article_id from comments where comments_id = '${comments_id}')) 
                     + 
                        (select count(*) from replys as rps inner join comments as cmt on rps.comments_id = cmt.comments_id 
                        and cmt.article_id = (select article_id from comments where comments_id = '${comments_id}'))
                    )
                )
                where articleId = (select article_id from comments where comments_id = '${comments_id}')
                `
                )

                await transaction.commit();
                return true;
            } catch (e) {
                await transaction.rollback();
                return false
            }
        }
        // 添加评论
    async sendComment(uid, articleid, content, date) {
        let transaction = await this.app.mysql.beginTransaction();
        try {
            const res = await transaction.insert('comments', {
                article_id: articleid,
                user_id: uid,
                comments_content: content,
                comments_date: date
            });
            const update = await transaction.query(
                `
                update articles set articleComments = 
                (
                    select sum(
                        (select count(*) from comments where article_id = '${articleid}') 
                        + 
                        (select count(*) from replys as rps inner join comments as cmt on rps.comments_id = cmt.comments_id and cmt.article_id = '${articleid}')
                    )
                )
                where articleId = '${articleid}'
                `
            )
            await transaction.commit();
            return true;
        } catch (e) {
            await transaction.rollback();
            return false;
        }
    };

    async getArticlesByKeyword(keyword, currentsize, size, uid) {
        try {
            const res = await this.app.mysql.query(
                `select 
                a.articleId, a.screenShot, a.title, a.articleSummaryInfo, a.articlePostTime, a.articleLikes, a.articleComments,
                u.uid,u.username,u.userAvatar,
                if(isnull(l.uId),0,1) as islike
                from (articles as a,users as u)
                LEFT JOIN likes as l
                ON l.articleId = a.articleId
                and l.uId = '${uid}'
                where a.uid =  u.uid
                and a.title like '%${keyword}%'  
                order by a.articlePostTime desc
                limit ${currentsize},${size}`
            );
            if (res) {
                return res;
            } else {
                return false
            }
        } catch (e) {
            return false;
        }
    };

    async getHotAuthor() {
        try {
            const res = this.app.mysql.query(
                `select 
                 u.uid,
                 u.username,
                 u.email,
                 u.userAvatar,
                 sum(articleLikes) as likeCount
                 from articles as a
                 join users as u
                 on u.uid = a.uid
                 where a.uid = u.uid
                 group by a.uid
                 order by likeCount desc
                 limit 5`
            );
            if (res) {
                return res;
            } else {
                return false
            }
        } catch (e) {
            return false
        }
    }

}

module.exports = UserService;